package com.techysky.service;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import javax.annotation.Resource;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.transaction.Transactional;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.techysky.config.IetmConfig;
import com.techysky.entity.ExamOnTheSpot;
import com.techysky.entity.Paper;
import com.techysky.entity.Question;
import com.techysky.entity.QuestionModel;
import com.techysky.entity.QuestionPaper;
import com.techysky.repository.ExamOnTheSpotRepository;
import com.techysky.repository.ExamRecordRepository;
import com.techysky.repository.PaperRepository;
import com.techysky.repository.QuestionModelRepository;
import com.techysky.repository.QuestionPaperRepository;


@Service
public class PaperManagerService{
	private static final String SUCCESS = "success";
	private static final Log log=LogFactory.getLog(PaperManagerService.class);
	@Resource
	private PaperRepository paperRepository;
	@Resource
	private QuestionPaperRepository questionPaperRepository;
	@Resource
	private ExamRecordRepository examRecordRepository;
	@Resource
	private QuestionModelRepository questionModelRepository;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private IetmConfig config;
	@Autowired
	private ExamOnTheSpotRepository examOnTheSpotRepository;
	//根据id查询
	@Transactional
	public Paper findById(String id){
		return paperRepository.findOne(id);
	}
	//保存数据
	@Transactional
	public void save(Paper paper){
		paperRepository.save(paper);
	}
	//查询数据.
	public Iterable<Paper> getAll(){
		return paperRepository.findAll();
	}
	
	@Transactional
	public String deletePapers(String ids){
		String[]  strs=ids.split(",");
		for(int i=0;i<strs.length;i++){
			paperRepository.delete(strs[i]);
		}
		return "1";
	}
	
	
public String findPaperByIds(String ids,String paperName) throws IOException{
		String[]  strs=ids.split(",");
		List<String> idsList=Arrays.asList(strs);
		List<Paper> paperList=paperRepository.findAll(idsList);
		String fileUrl=config.getPaperPath()+paperName+".txt";
		String changeLine="'\r\n";
		//////////写入文件
		File f = new File(fileUrl);      
		if (!f.exists())   
		{       
			f.createNewFile();      
		}      
		OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream(f),"utf-8");      
		BufferedWriter bw=new BufferedWriter(fw);
		String sqlstr="";
		for (int i = 0; i < paperList.size(); i++) {
			Paper paper=paperList.get(i);
			Set<QuestionPaper> set= paper.getQuestionPaper();
			Iterator<QuestionPaper> it = set.iterator(); 
			
			String allStr="";
			String uuid=config.getPaperContent();
			//删除已经存在的试卷试题关联表
			String delQuestionPaperbyPaperId=" delete from SYS_QUESTION_PAPER where paper_id='"+paper.getId()+changeLine+uuid;
			bw.write(delQuestionPaperbyPaperId);
			//删除已经存在的试卷
			String delparperStrSql=" delete from SYS_PAPER where id='"+paper.getId()+changeLine+uuid;
			bw.write(delparperStrSql);
			while (it.hasNext()) {
				allStr = comm(changeLine, bw, it, allStr, uuid);
			}  
			String parperStrSql=" INSERT INTO SYS_PAPER(ID,NAME,ASK_COUNT,ASK_SCORE,CHECK_COUNT,CHECK_SCORE,EXAM_TYPE,JUDGE_COUNT,JUDGE_SCORE,RADIO_COUNT,RADIO_SCORE,TOTAL_SCORE,TOTAL_TIME,CREATE_DATE,CREATE_USER) VALUES (";
				   parperStrSql+="'"+paper.getId()+"','"+paper.getName()+"',"+paper.getAskCount()+","+paper.getAskScore()+","+paper.getCheckCount()+","+paper.getCheckScore()+",'"
					+paper.getExamType()+"',"+paper.getJudgeCount()+","+paper.getJudgeScore()+","+paper.getRadioCount()+","+paper.getRadioScore()+","+paper.getTotalScore()+","+paper.getTotalTime()+",'"+paper.getCreateDate()+"','"+paper.getCreateUser()+"')\r\n"+uuid;
			sqlstr+=parperStrSql+allStr;
			bw.write(sqlstr);
			
		}
		
		bw.close();
		fw.close();
		//////////写入文件
		return fileUrl;
	}
private String comm(String changeLine, BufferedWriter bw, Iterator<QuestionPaper> it, String allStr, String uuid)
		throws IOException {
	QuestionPaper questionPaper = it.next();  
	Question question=questionPaper.getQuestion();
	//删除已经存在的试题关联章节的关系表
	String delQuestionModel=" delete from sys_question_model  where question_id='"+question.getId()+changeLine+uuid;
	bw.write(delQuestionModel);
	//删除已经存在的试题
	String delquestionStrSql=" delete from SYS_QUESTION where id='"+question.getId()+changeLine+uuid;
	bw.write(delquestionStrSql);
	Question q=emptyOrNull(question);
	String questionStrSql="";
	String questionPaperStrSql="";
	String questionModel=""; 
	questionStrSql+=delquestionStrSql+" INSERT INTO SYS_QUESTION(ID,CODE,CREATE_DATE,CREATE_USER,A,B,C,D,E,ANSWER,CONTENT,SEXPLAIN) VALUES ("+"'"+question.getId()+"',"+q.getCode()+","+q.getCreateDate()+","+q.getCreateUser()+","+q.getA()+","+q.getB()+","+q.getC()+","+q.getD()+","+q.getE()+","+q.getAnswer()+","+q.getContent()+","+q.getExplain()+")\r\n"+uuid;
	questionModel = qm(changeLine, questionModel, uuid, question);
	questionPaperStrSql+=" INSERT INTO SYS_QUESTION_PAPER(ID,ORDER_CODE,PAPER_ID,QUESTION_ID) VALUES ("+"'"+questionPaper.getId()+"','"+questionPaper.getOrderCode()+"','"+questionPaper.getPaper().getId()+"','"+question.getId()+"'"+")\r\n"+uuid;
	return allStr+questionModel+questionStrSql+questionPaperStrSql;
}
private Question emptyOrNull(Question question) {
	question.setA(strcomm(question.getA()));
	question.setB(strcomm(question.getB()));
	question.setC(strcomm(question.getC()));
	question.setD(strcomm(question.getD()));
	question.setE(strcomm(question.getE()));
	question.setAnswer(strcomm(question.getAnswer()));
	question.setCode(strcomm(question.getCode()));
	question.setCreateUser(strcomm(question.getCreateUser()));
	question.setCreateDate(strcomm(question.getCreateDate()));
	question.setContent(strcomm(question.getContent()));
	question.setExplain(strcomm(question.getExplain()));
	return question;
}
private String strcomm(Object obj) {
	String str=null;
	if(null!=obj){
		str="'"+obj+"'";
	}
	return str;
}

	/**
	 * public String findPaperByIds(String ids) throws IOException{
		String[]  strs=ids.split(",");
		List<String> idsList=Arrays.asList(strs);
		List<Paper> paperList=paperRepository.findAll(idsList);
		String fileUrl=config.getPaperPath();
		String changeLine="'\r\n";
		//////////写入文件
		File f = new File(fileUrl);      
		if (!f.exists())   
		{       
			f.createNewFile();      
		}      
		OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream(f),"utf-8");      
		BufferedWriter bw=new BufferedWriter(fw);
		String sqlstr="";
		for (int i = 0; i < paperList.size(); i++) {
			Paper paper=paperList.get(i);
			Set<QuestionPaper> set= paper.getQuestionPaper();
			Iterator<QuestionPaper> it = set.iterator(); 
			String questionStrSql="";
			String questionPaperStrSql="";
			String questionModel="";
			
			String uuid=config.getPaperContent();
			//删除已经存在的试卷试题关联表
			String delQuestionPaperbyPaperId=" delete from SYS_QUESTION_PAPER where paper_id='"+paper.getId()+changeLine+uuid;
			bw.write(delQuestionPaperbyPaperId);
			//删除已经存在的试卷
			String delparperStrSql=" delete from SYS_PAPER where id='"+paper.getId()+changeLine+uuid;
			bw.write(delparperStrSql);
			while (it.hasNext()) {
				QuestionPaper questionPaper = it.next();  
				Question question=questionPaper.getQuestion();
				//删除已经存在的试题关联章节的关系表
				String delQuestionModel=" delete from sys_question_model  where question_id='"+question.getId()+changeLine+uuid;
				bw.write(delQuestionModel);
				//删除已经存在的试题
				String delquestionStrSql=" delete from SYS_QUESTION where id='"+question.getId()+changeLine+uuid;
				bw.write(delquestionStrSql);
				String qa=null!=question.getA()?"'"+question.getA()+"'":null;
				String qb=null!=question.getB()?"'"+question.getB()+"'":null;
				String qc=null!=question.getC()?"'"+question.getC()+"'":null;
				String qd=null!=question.getD()?"'"+question.getD()+"'":null;
				String qe=null!=question.getE()?"'"+question.getE()+"'":null;
				String answer=null!=question.getAnswer()?"'"+question.getAnswer()+"'":null;
				String code=null!=question.getCode()?"'"+question.getCode()+"'":null;
				String createDate=null!=question.getCreateDate()?"'"+question.getCreateDate()+"'":null;
				String createUser=null!=question.getCreateUser()?"'"+question.getCreateUser()+"'":null;
				String content=null!=question.getContent()?"'"+question.getContent()+"'":null;
				String explain=null!=question.getExplain()?"'"+question.getExplain()+"'":null;
				questionStrSql+=delquestionStrSql+" INSERT INTO SYS_QUESTION(ID,CODE,CREATE_DATE,CREATE_USER,A,B,C,D,E,ANSWER,CONTENT,SEXPLAIN) VALUES ("
						+"'"+question.getId()+"',"+code+","+createDate+","+createUser+","+qa
						+","+qb+","+qc+","+qd+","+qe+","+answer+","+content+","+explain+")\r\n"+uuid;
				questionModel = qm(changeLine, questionModel, uuid, question);
				questionPaperStrSql+=" INSERT INTO SYS_QUESTION_PAPER(ID,ORDER_CODE,PAPER_ID,QUESTION_ID) VALUES ("
						+"'"+questionPaper.getId()+"','"+questionPaper.getOrderCode()+"','"+questionPaper.getPaper().getId()+"','"+question.getId()+"'"+")\r\n"+uuid;
			}  
			String parperStrSql=" INSERT INTO SYS_PAPER(ID,NAME,ASK_COUNT,ASK_SCORE,CHECK_COUNT,CHECK_SCORE,EXAM_TYPE,JUDGE_COUNT,JUDGE_SCORE,RADIO_COUNT,RADIO_SCORE,TOTAL_SCORE,TOTAL_TIME,CREATE_DATE,CREATE_USER) VALUES (";
			parperStrSql+="'"+paper.getId()+"','"+paper.getName()+"',"+paper.getAskCount()+","+paper.getAskScore()+","+paper.getCheckCount()+","+paper.getCheckScore()+",'"
					+paper.getExamType()+"',"+paper.getJudgeCount()+","+paper.getJudgeScore()+","+paper.getRadioCount()+","+paper.getRadioScore()+","+paper.getTotalScore()+","+paper.getTotalTime()+",'"+paper.getCreateDate()+"','"+paper.getCreateUser()+"')\r\n"+uuid;
			sqlstr+=questionModel+questionStrSql+parperStrSql+questionPaperStrSql;
			bw.write(sqlstr);
			
		}
		
		bw.close();
		fw.close();
		//////////写入文件
		return fileUrl;
	}
	 */
	
	 private String qm(String changeLine, String questionModel, String uuid, Question question) {
		//试题关联章节
		  List<QuestionModel> qms=questionModelRepository.findModelByQuestionId(question.getId());
		  String qmstr="";
		 for (QuestionModel qm : qms) {
			 String questionModelById=" delete from sys_question_model  where id ='"+qm.getId()+changeLine+uuid;
			 qmstr+=questionModel+questionModelById+" INSERT INTO sys_question_model(id,question_id,model_id,model_dm_code) VALUES ('"
						+qm.getId()+"','"+qm.getQuestionId()+"','"+qm.getModelId()+"','"+qm.getModelDmCode()+"')\r\n"+uuid;
		 	}
		 //试题关联章节
		return qmstr;
	}
	
	/**
     * 读取txt文件的内容
     * @param file 想要读取的文件对象
     * @return 返回文件内容
     */
    public  String txt2String(File file){
    	 String fileContent = "";    
    	 try   
    	    {       
    	            
    	        if(file.isFile()&&file.exists())  
    	        {       
    	            InputStreamReader read = new InputStreamReader(new FileInputStream(file),"utf-8");       
    	            BufferedReader reader=new BufferedReader(read);       
    	            String line;       
    	            while ((line = reader.readLine()) != null)   
    	            {        
    	                fileContent += line+"\r\n  ";       
    	            }         
    	            read.close();      
    	        }     
    	    } catch (Exception e)   
    	    {         
    	        log.info(e);    
    	    }     
    	 String[] sql=fileContent.split(config.getPaperContent());
    	
    	 List<String> tmp = new ArrayList<>();
         for(String str:sql){
        	 String ss=str.trim();
             if(ss!=null && ss.length()!=0){
                tmp.add(ss);
             }
         }
        String[] sqlStr=tmp.toArray(new String[0]);
         try {
			jdbcTemplate.batchUpdate(sqlStr);
			
		} catch (DataAccessException e) {
			log.info(e);   
		}
    
    	    return SUCCESS;
      
    }
  
    
  
   
	
	
	public String saveOrUpadte(Paper paper) {
	    this.paperRepository.saveAndFlush(paper);
		return SUCCESS;
	}
	public String saveOrUpadte(ExamOnTheSpot examOnTheSpot) {
		this.examOnTheSpotRepository.save(examOnTheSpot);
		return SUCCESS;
	}
	public List<Paper> findPapersByExamType(String examType) {
		return this.paperRepository.findPapersByExamType(examType);
		
	}
	public void delExaminationOnTheSpot(String ids) {
		String[]  strs=ids.split(",");
		for(int i=0,len=strs.length;i<len;i++){
			examOnTheSpotRepository.delete(strs[i]);
		}
	}
	public ExamOnTheSpot findExaminationOnTheSpot(String id) {
		return examOnTheSpotRepository.findOne(id);
	}
	public Page<Paper> findByPage(PageRequest pageable, String search) {
		return paperRepository.findAll((Root<Paper> root, CriteriaQuery<?> query, CriteriaBuilder cb) ->{
				List<Predicate> predicates = new ArrayList<>();
				 if (!StringUtils.isEmpty(search)) {
					 
				 Predicate p1=cb.like(root.get("name").as(String.class), "%" + search + "%");
				 Predicate p2=cb.like(root.get("examType").as(String.class), "%" + search + "%");
				 predicates.add(cb.or(p1,p2));
				 }
				 query.orderBy(cb.desc(root.get("createDate")));
				 Predicate[] pre = new Predicate[predicates.size()];
				return query.where(predicates.toArray(pre)).getRestriction();
			}
		, pageable);
	}
	
	
	
	public Page<ExamOnTheSpot> findExamOnTheSpot(PageRequest pageable, String search) {
		return examOnTheSpotRepository.findAll((Root<ExamOnTheSpot> root, CriteriaQuery<?> query, CriteriaBuilder cb)-> {
				List<Predicate> predicates = new ArrayList<>();
				if (!StringUtils.isEmpty(search)) {
					predicates.add(cb.like(root.get("user").get("userName").as(String.class), "%" + search + "%"));
				}
				query.orderBy(cb.desc(root.get("createDate")));
				Predicate[] pre = new Predicate[predicates.size()];
				return query.where(predicates.toArray(pre)).getRestriction();
			}
			, pageable);
	}
	public List<Object> findExaminationOnTheSpotUser(String paperId) {
		return examOnTheSpotRepository.findExaminationOnTheSpotUser(paperId);
		
	}
}
